package dao;

import model.User;
import util.DatabaseConnection;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 * 用户数据访问对象
 */
public class UserDAO {

    /**
     * 用户登录验证
     */
    public User login(String studentId, String password) throws SQLException {
        String sql = "SELECT * FROM users WHERE student_id = ? AND password = ?";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, studentId);
            stmt.setString(2, password);

            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return mapResultSetToUser(rs);
            }
        }
        return null;
    }

    /**
     * 检查学号是否已存在
     */
    public boolean existsByStudentId(String studentId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM users WHERE student_id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, studentId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        }
        return false;
    }

    /**
     * 插入新用户
     */
    public boolean insertUser(User user) throws SQLException {
        String sql = "INSERT INTO users (student_id, name, password, phone, email, user_type, credit_score, is_banned, create_time) "
                +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, user.getStudentId());
            stmt.setString(2, user.getName());
            stmt.setString(3, user.getPassword());
            stmt.setString(4, user.getPhone());
            stmt.setString(5, user.getEmail());
            stmt.setInt(6, user.getUserType());
            stmt.setInt(7, user.getCreditScore() > 0 ? user.getCreditScore() : 100); // 默认100分
            stmt.setBoolean(8, user.isBanned());
            stmt.setTimestamp(9, Timestamp.valueOf(LocalDateTime.now()));

            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 更新用户信息
     */
    public boolean updateUser(User user) throws SQLException {
        String sql = "UPDATE users SET name = ?, password = ?, phone = ?, email = ? WHERE user_id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, user.getName());
            stmt.setString(2, user.getPassword());
            stmt.setString(3, user.getPhone());
            stmt.setString(4, user.getEmail());
            stmt.setInt(5, user.getUserId());

            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 获取所有用户
     */
    public List<User> getAllUsers() throws SQLException {
        String sql = "SELECT * FROM users ORDER BY create_time DESC";
        List<User> users = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql);
                ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                users.add(mapResultSetToUser(rs));
            }
        }
        return users;
    }

    /**
     * 搜索用户
     */
    public List<User> searchUsers(String keyword) throws SQLException {
        String sql = "SELECT * FROM users WHERE student_id LIKE ? OR name LIKE ? OR phone LIKE ? " +
                "ORDER BY create_time DESC";
        List<User> users = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            String searchKeyword = "%" + keyword + "%";
            stmt.setString(1, searchKeyword);
            stmt.setString(2, searchKeyword);
            stmt.setString(3, searchKeyword);

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                users.add(mapResultSetToUser(rs));
            }
        }
        return users;
    }

    /**
     * 重置密码
     */
    public boolean resetPassword(int userId, String newPassword) throws SQLException {
        String sql = "UPDATE users SET password = ? WHERE user_id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, newPassword);
            stmt.setInt(2, userId);

            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 删除用户
     */
    public boolean deleteUser(int userId) throws SQLException {
        String sql = "DELETE FROM users WHERE user_id = ? AND user_type = 0"; // 只能删除学生

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 更新用户信誉分
     */
    public boolean updateUserCreditScore(int userId, int creditScore) throws SQLException {
        String sql = "UPDATE users SET credit_score = LEAST(150, GREATEST(0, ?)) WHERE user_id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, creditScore);
            stmt.setInt(2, userId);

            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 禁用用户
     */
    public boolean banUser(int userId, java.time.LocalDate banUntilDate) throws SQLException {
        String sql = "UPDATE users SET is_banned = TRUE, ban_until_date = ? WHERE user_id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            if (banUntilDate != null) {
                stmt.setDate(1, Date.valueOf(banUntilDate));
            } else {
                stmt.setNull(1, Types.DATE);
            }
            stmt.setInt(2, userId);

            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 解禁用户
     */
    public boolean unbanUser(int userId) throws SQLException {
        String sql = "UPDATE users SET is_banned = FALSE, ban_until_date = NULL WHERE user_id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            return stmt.executeUpdate() > 0;
        }
    }

    /**
     * 获取信誉分排行榜
     */
    public List<User> getCreditScoreRanking(int limit) throws SQLException {
        String sql = "SELECT * FROM users WHERE user_type = 0 ORDER BY credit_score DESC LIMIT ?";
        List<User> users = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, limit);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                users.add(mapResultSetToUser(rs));
            }
        }
        return users;
    }

    /**
     * 获取被禁用的用户列表
     */
    public List<User> getBannedUsers() throws SQLException {
        String sql = "SELECT * FROM users WHERE user_type = 0 AND is_banned = TRUE ORDER BY ban_until_date DESC";
        List<User> users = new ArrayList<>();

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                users.add(mapResultSetToUser(rs));
            }
        }
        return users;
    }

    /**
     * 将ResultSet映射为User对象
     */
    private User mapResultSetToUser(ResultSet rs) throws SQLException {
        User user = new User();
        user.setUserId(rs.getInt("user_id"));
        user.setStudentId(rs.getString("student_id"));
        user.setName(rs.getString("name"));
        user.setPassword(rs.getString("password"));
        user.setPhone(rs.getString("phone"));
        user.setEmail(rs.getString("email"));
        user.setUserType(rs.getInt("user_type"));

        // 处理信誉分相关字段
        user.setCreditScore(rs.getInt("credit_score"));
        user.setBanned(rs.getBoolean("is_banned"));

        Date banUntilDate = rs.getDate("ban_until_date");
        if (banUntilDate != null) {
            user.setBanUntilDate(banUntilDate.toLocalDate());
        }

        Timestamp createTime = rs.getTimestamp("create_time");
        if (createTime != null) {
            user.setCreateTime(createTime.toLocalDateTime());
        }

        return user;
    }
}
